package com.clusterswisdom.horn.api.tmall.action.alipay;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.clusterswisdom.horn.api.db.DbUtil;
import com.clusterswisdom.horn.api.ioc.HornAction;
import com.clusterswisdom.horn.api.ioc.HornResponse;
import com.clusterswisdom.horn.api.ioc.api.ApiConfig;
import com.clusterswisdom.horn.api.tmall.TmallInstance;
import com.clusterswisdom.horn.api.tmall.action.AbstractTmallAction;
import com.clusterswisdom.horn.api.tmall.action.alipay.free.FreeUtil;
import com.clusterswisdom.horn.api.tmall.action.alipay.free.TmallFree;
import com.clusterswisdom.horn.api.tmall.action.alipay.free.TmallTrade;
import com.taobao.api.domain.AlipayRecord;
import com.taobao.api.response.AlipayUserAccountreportGetResponse;
import com.taobao.api.response.TradeGetResponse;

public class GetUserTradeAction extends AbstractTmallAction implements HornAction {
	
	private Logger log = LoggerFactory.getLogger(GetUserTradeAction.class);
	
	@Override
	public HornResponse execute(ApiConfig config, JSONObject param) throws Exception {
		Calendar startTime = getBeginDate(param);
		Calendar endTime = getEndDate(param);
		// 账期
		
		String channel = TmallInstance.getInstance().getConfig().getChannel();
		try {
			Long tradeId = TraderDBUtil.addBill(channel, getBillDateStr(param), getBillSpanStr(param),getBillDate(param));
			writeOrderIntoDb(startTime, endTime, tradeId);
			return new HornResponse(true, tradeId);
			
		} catch (Exception e) {
			DbUtil.getUtil().rollback();
			throw e;
			 
		} finally {
			DbUtil.getUtil().commit();
			
		}
	}

	/**
	 * 将实收金额，费用总额,差额写到数据库中
	 * 
	 * @throws SQLException
	 * @throws Exception
	 */
	public void writeMoneyIntoDb(Long tradeId) throws Exception {
		Connection con = DbUtil.getUtil().getConnection();
		String sql = "select torderid, countMoney from OrderAccount where billSpanStrId = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setLong(1, tradeId);
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			String torderid = rs.getString("torderid");
			// 实收
			double countMoney = rs.getDouble("countMoney");
			String sqlOfFee = "select feevalue from fee where torderid = ? and billSpanStrId = ?";
			PreparedStatement psof = con.prepareStatement(sqlOfFee);
			psof.setString(1, torderid);
			psof.setLong(2,tradeId);
			ResultSet rsof = psof.executeQuery();
			
			// 费用总额
			double feeMoney = 0;
			while (rsof.next()) {
				double feeValue = rsof.getDouble("feevalue");
				feeMoney += feeValue;
			}
			rsof.close();
			JSONObject param = new JSONObject();
			param.put("tid", torderid);
			TradeGetResponse response = (TradeGetResponse) TmallInstance.getInstance().executeRequest(param, "trade", "tradeGet");
			// 应收金额
			String payedMoney = response.getTrade().getReceivedPayment();
			//运费
			double busShippingAmount = Double.parseDouble(response.getTrade().getPostFee());
			// 差额
			double differFee = Double.parseDouble(payedMoney) - countMoney;

			String updateSql = "update OrderAccount set totalMoney = ? ,feeMoney = ? ,differFee = ? , busShippingAmount = ? where torderid = ?";
			PreparedStatement updatePs = con.prepareStatement(updateSql);
			updatePs.setDouble(1,Double.parseDouble(payedMoney));
			updatePs.setDouble(2, feeMoney);
			updatePs.setDouble(3, differFee);
			updatePs.setDouble(4,busShippingAmount);
			updatePs.setString(5, torderid);
			updatePs.execute();
			updatePs.close();
		}
		
		rs.close();
	}

	/**
	 * 根据时间获取交易记录，时间间隔小于1天的,并且入库
	 * 
	 * @param startTime
	 * @param endTime
	 * @return
	 * @throws Exception
	 */
	public void getAlipayRecord(Calendar startTime, Long tradId) throws Exception {
		JSONObject param = new JSONObject();
		param.put("start_time", startTime.getTime());
		
		startTime.add(Calendar.DAY_OF_YEAR, 1);
		param.put("end_time", startTime.getTime());
		
		int page = 1;
		boolean isNotEnd = true;
		do {
			param.put("page_no", page);
			AlipayUserAccountreportGetResponse response = (AlipayUserAccountreportGetResponse) TmallInstance.getInstance().executeRequest(param, "alipay", "alipayRecord");
			log.debug("get page AlipayUserAccountreport : " + DateFormatUtils.format(startTime, "yyyy-MM-dd") + ";" + page + "; datas " + response.isSuccess() + ";" + response.getAlipayRecords());
			if (response.isSuccess() && response.getAlipayRecords() != null) {
				writeAlipayRecordIntoDb(response.getAlipayRecords(), tradId);
			}
			
			Long totalPages = response.getTotalPages();
			if (totalPages == null) {
				totalPages = 1l;
			}
			
			isNotEnd = page < totalPages;
			page++;

		} while (isNotEnd);
	}

	/**
	 * 将交易的费用和交易写到对应数据库中
	 * 
	 * @param alipayRecords
	 * @throws SQLException
	 */
	public void writeAlipayRecordIntoDb(List<AlipayRecord> alipayRecords, Long tradeId) throws SQLException {
		Connection con = DbUtil.getUtil().getConnection();
		for (AlipayRecord alipayrecord : alipayRecords) {
			log.debug("get alipay record : " + JSON.toJSONString(alipayrecord));
			
			// 如果子业务类型为transfer_01或者payment_02则写入到订单的表中，否则写入费用表中
			if ((alipayrecord.getBusinessType().equals("transfer_01") || alipayrecord.getBusinessType().equals("payment_02") ||(alipayrecord.getBusinessType().equals("other") && alipayrecord.getType().equals("deposit")))&& alipayrecord.getInAmount() != null) {
				String selectSql = "select * from OrderAccount where torderid = ? and billSpanStrId = ?";
				PreparedStatement selectPs = con.prepareStatement(selectSql);
				selectPs.setString(1, FreeUtil.getTradId(alipayrecord .getMerchantOrderNo()));
				selectPs.setLong(2, tradeId);
				ResultSet rs = selectPs.executeQuery();
				if(!rs.next()){
					String sql = "insert into OrderAccount(torderid, countMoney, billSpanStrId)values(? , ? , ?)";
					PreparedStatement ps = con.prepareStatement(sql);
					ps.setString(1, FreeUtil.getTradId(alipayrecord .getMerchantOrderNo()));
					ps.setDouble(2, Double.parseDouble(alipayrecord.getInAmount()));
					ps.setLong(3, tradeId);
					ps.execute();
					log.debug("transferMoney = " + alipayrecord.getInAmount() + " torderid = " + FreeUtil.getTradId(alipayrecord .getMerchantOrderNo()));
					ps.close();
					
				}else{
					String updateSql = "update OrderAccount set `countMoney` = `countMoney`+?  where torderid = ? and billSpanStrId = ? ";
					PreparedStatement updatePs = con.prepareStatement(updateSql);
					updatePs.setDouble(1, Double.parseDouble(alipayrecord.getInAmount()));
					updatePs.setString(2, FreeUtil.getTradId(alipayrecord .getMerchantOrderNo()));
					updatePs.setLong(3, tradeId);
					updatePs.execute();
					log.debug("paymentMoney = " + alipayrecord.getInAmount() + " torderid = " + FreeUtil.getTradId(alipayrecord .getMerchantOrderNo()));
					updatePs.close();
					
				}
				selectPs.close();
			} else if (alipayrecord.getMemo() != null && alipayrecord.getOutAmount() != null) {
				//将银行卡转帐单独取出存放
				if(FreeUtil.getFreeInfos(alipayrecord.getMemo())[0] != null){
					String sql = "insert into fee(torderid, feecode, feeName, feevalue, billSpanStrId) values(?, ?, ?, ?, ?)";
					PreparedStatement ps = con.prepareStatement(sql);
	
					String[] infos = FreeUtil.getFreeInfos(alipayrecord.getMemo());
					ps.setString(1, infos[0]);
					ps.setString(2, infos[2]);
					ps.setString(3, infos[1]);
					ps.setDouble(4, Double.parseDouble(alipayrecord.getOutAmount()));
					ps.setLong(5,tradeId);
					ps.execute();
					ps.close();
				}else if(alipayrecord.getType().equals("withdraw") && alipayrecord.getOutAmount() != null){
					String sql = "insert into transtobank(money ,billspanstrid) values(?, ?)";
					PreparedStatement ps = con.prepareStatement(sql);
	
					ps.setDouble(1, Double.parseDouble(alipayrecord.getOutAmount()));
					ps.setLong(2,tradeId);
					ps.execute();
					ps.close();
					
					
				}
			}
		}
	}

	/**
	 * 将一个月账期的数据写如到数据库中
	 * 
	 * @throws Exception
	 */
	public void writeOrderIntoDb(Calendar startTime, Calendar endTime, Long tradeId) throws Exception {
		while (startTime.getTimeInMillis() <= endTime.getTimeInMillis()) {
			getAlipayRecord(startTime, tradeId);
		}
		DbUtil.getUtil().commit();

		writeMoneyIntoDb(tradeId);
	}

	public List<TmallTrade> getTmallTrades(String billSpanStr) throws SQLException {
		List<TmallTrade> tmallTrades = new LinkedList<TmallTrade>();
		Connection con = DbUtil.getUtil().getConnection();
		String sql = "select * from  OrderAccount where billSpanStr = ?";
		PreparedStatement ps = con.prepareStatement(sql);
		ps.setString(1, billSpanStr);
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			TmallTrade tmallTrade = new TmallTrade();
			tmallTrade.setCountMoney(rs.getDouble("CountMoney"));
			tmallTrade.setDifferFee(rs.getDouble("differFee"));
			tmallTrade.setFeeMoney(rs.getDouble("feeMoney"));
			tmallTrade.setTorderId(rs.getString("torderId"));
			tmallTrade.setTotalMoney(rs.getDouble("totalMoney"));
			List<TmallFree> tmallFrees = new LinkedList<TmallFree>();
			String freeSql = "Select * from fee where torderid = ?";
			PreparedStatement freePs = con.prepareStatement(freeSql);
			freePs.setString(1, rs.getString("torderid"));
			ResultSet freeRs = freePs.executeQuery();
			while (freeRs.next()) {
				TmallFree tmallFree = new TmallFree();
				tmallFree.setFree(freeRs.getDouble("feevalue"));
				tmallFree.setFreeType(freeRs.getString("feecode"));
				tmallFree.setOrderId(freeRs.getString("torderid"));
				tmallFree.setTitle(freeRs.getString("feeName"));
				tmallFrees.add(tmallFree);
			}
			tmallTrade.setTmallFreeList(tmallFrees);
			tmallTrades.add(tmallTrade);
		}
		
		return tmallTrades;
	}

	protected String getBillDateStr(JSONObject param) {
		Calendar begin = getBeginDate(param);

		StringBuilder builder = new StringBuilder();
		if(begin.get(Calendar.MONTH)+2 >12){
			builder.append(begin.get(Calendar.YEAR)+1);
			builder.append("年-");
			builder.append(01);
			builder.append("月");
			
		}else{
			builder.append(begin.get(Calendar.YEAR));
			builder.append("年-");
			if((begin.get(Calendar.MONTH) + 2) < 10){
				builder.append("0"+(begin.get(Calendar.MONTH) + 2));
				
			}else{
				builder.append((begin.get(Calendar.MONTH) + 2));
				
			}
			
			builder.append("月");
			
		}
		
		return builder.toString();
	}

	protected String getBillSpanStr(JSONObject param) {
		Calendar begin = getBeginDate(param);
		Calendar end = getEndDate(param);

		StringBuilder builder = new StringBuilder();
		builder.append(DateFormatUtils.format(begin, "yyyy-MM-dd"));
		builder.append("--");
		builder.append(DateFormatUtils.format(end, "yyyy-MM-dd"));

		return builder.toString();
	}

	private Calendar getBeginDate(JSONObject param) {
		Calendar calendar = param == null ? null : GregorianCalendar.getInstance();
		Date begin = param.getDate("start_time");
		if (begin != null) {
			calendar.setTime(begin);

		} else {
			calendar.set(Calendar.DAY_OF_MONTH, 25);
			calendar.add(Calendar.MONTH, -1);

		}

		calendar.set(Calendar.HOUR_OF_DAY, 0);
		calendar.set(Calendar.MINUTE, 0);
		calendar.set(Calendar.SECOND, 0);
		calendar.set(Calendar.MILLISECOND, 0);

		return calendar;
	}

	private Calendar getEndDate(JSONObject param) {
		Date end = param == null ? null : param.getDate("end_time");
		Calendar calendar = GregorianCalendar.getInstance();
		if (end != null) {
			calendar.setTime(end);

		} else {
			calendar.set(Calendar.DAY_OF_MONTH, 24);

		}

		calendar.set(Calendar.HOUR_OF_DAY, 23);
		calendar.set(Calendar.MINUTE, 59);
		calendar.set(Calendar.SECOND, 59);
		calendar.set(Calendar.MILLISECOND, 999);

		return calendar;
	}
	
	private String getBillDate(JSONObject param){
		Calendar begin = getBeginDate(param);

		StringBuilder builder = new StringBuilder();
		if(begin.get(Calendar.MONTH)+2 >12){
			builder.append(begin.get(Calendar.YEAR)+1);
			builder.append(01);
			
		}else{
			builder.append(begin.get(Calendar.YEAR));
			if((begin.get(Calendar.MONTH) + 2) < 10){
				builder.append("0"+(begin.get(Calendar.MONTH) + 2));
				
			}else{
				builder.append((begin.get(Calendar.MONTH) + 2));
				
			}
			
			
		}
		
		return builder.toString();
	}
}
